﻿Imports System.Data
Imports System.Data.OleDb

Public Class frmEditAufgabe
    Dim SQLStr As String
    Private ConnString As String
    Dim _aufgaben As Projekt = Projekt.GetObject()

    Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=|DataDirectory|\db.accdb")
    Dim reader As OleDbDataReader
    Dim cmd As New OleDbCommand

    Private Sub frmEditAufgabe_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        If (_aufgaben.aufgabeEdit) Then
            SQLStr = "SELECT * FROM [Aufgaben] WHERE [PK_Aufgaben] = " + _aufgaben.aufgabenrowId
            cmd.CommandText = SQLStr
            cmd.Connection = conn
            cmd.Connection.Open()
            reader = cmd.ExecuteReader()

            Do While reader.Read()
                txbTitel.Text = reader("Titel").ToString()
                txbAufgabenNr.Text = reader("Aufgaben_Nr").ToString()
                txbSoll.Text = reader("Soll-Aufwand").ToString()
            Loop
            cmd.Connection.Close()


        End If
        readProject()
    End Sub

    Private Sub readProject()
        SQLStr = "SELECT [Titel] FROM [Projekte] WHERE [PK_Projekte] = " + _aufgaben.projectrowId
        cmd.CommandText = SQLStr
        cmd.Connection = conn
        cmd.Connection.Open()
        reader = cmd.ExecuteReader()

        Do While reader.Read()
            txbProjekt.Text = reader("Titel").ToString()
        Loop
        cmd.Connection.Close()
        txbProjekt.Enabled = False

    End Sub

    Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
        If (_aufgaben.aufgabeEdit = True) Then
            SQLStr = "UPDATE [Aufgaben] SET [FK_Projekte] = " + _aufgaben.projectrowId + ", [Titel] = '" + txbTitel.Text + "', [Aufgaben_Nr] = " + txbAufgabenNr.Text + ", [Soll-Aufwand] = " + txbSoll.Text + ", [Status] = 0 Where [PK_Aufgaben] = " + _aufgaben.aufgabenrowId + ";"
        Else
            SQLStr = "INSERT Into [Aufgaben] ([FK_Projekte], [Titel], [Aufgaben_Nr], [Soll-Aufwand], [Status]) VALUES(" + _aufgaben.projectrowId + ", '" + txbTitel.Text + "', " + txbAufgabenNr.Text + ", " + txbSoll.Text + ", 0)"
        End If

        'SQLStr = "SELECT * FROM tblQuestion"


        'SQLStr = "UPDATE tblQuestion SET Answer = 'Like this' Where Question = 'How to use SQL?'"

        'SQLStr = "DELETE FROM tblQuestion WHERE Question='How to use SQL?'"
        executeQuery(SQLStr)

    End Sub

    Private Sub executeQuery(ByVal sqlquery As String)
        Try
            cmd.CommandText = SQLStr
            cmd.Connection = conn
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
            cmd.Connection.Close()
            Me.Close()
        Catch ex As Exception
            MsgBox(ex.ToString())
        End Try
    End Sub

    Private Sub frmEditAufgabe_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
        _aufgaben.aufgabeEdit = False
        frmShowAufgaben.Show()
    End Sub
End Class